package com.cuxiaoyi.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import javax.naming.NamingException;

import com.cuxiaoyi.model.User;
import com.cuxiaoyi.utils.PagingOption;
import com.cuxiaoyi.utils.PagingResult;
import com.taobao.api.domain.Item;
import com.taobao.api.domain.Promotion;

public class Dao {
	
	static Logger _logger = Logger.getLogger(Dao.class.getName());
	
	public static final Dao INSTANCE = new Dao();
	
	private Dao()
	{
		
	}
	
	public String addItem(Item item, Connection conn) throws Exception
	{
		String sql = "insert into item_t(num_iid_c, pic_url_c, title_c) " +
				"values(?,?,?)";
		PreparedStatement statement = conn.prepareStatement(sql);
		statement.setLong(1, item.getNumIid());
		statement.setString(2, item.getPicUrl());
		statement.setString(3, item.getTitle());
		statement.executeUpdate();
		statement.close();
		return null;
	}
	
	public String deleteItem(long numIid, Connection conn) throws Exception
	{
		String sql = "delete from merged_item_t where num_iid_c=?";
		PreparedStatement statement = conn.prepareStatement(sql);
		statement.setLong(1, numIid);
		statement.executeUpdate();
		statement.close();
		return null;
	}
	
	public String addPromotion(long userId, Promotion promotion, Connection conn) throws Exception
	{
		String sql = "insert into promotion_t(id_c,num_iid_c,user_id_c,discount_type_c," +
				"discount_value_c,start_date_c,end_date_c,tag_id_c,title_c,description_c," +
				"status_c) values(?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement statement = conn.prepareStatement(sql);
		statement.setLong(1, promotion.getPromotionId());
		statement.setLong(2, promotion.getNumIid());
		statement.setLong(3, userId);
		statement.setString(4, promotion.getDiscountType());
		statement.setString(5, promotion.getDiscountValue());
		statement.setTimestamp(6, new Timestamp(promotion.getStartDate().getTime()));
		statement.setTimestamp(7, new Timestamp(promotion.getEndDate().getTime()));
		statement.setLong(8, promotion.getTagId());
		statement.setString(9, promotion.getPromotionTitle());
		statement.setString(10, promotion.getPromotionDesc());
		statement.setString(11, promotion.getStatus());
		statement.executeUpdate();
		statement.close();
		return null;
	}
	
	public String updatePromotion(Promotion promotion, Connection conn) throws Exception
	{
		String sql = "update promotion_t set discount_type_c=?," +
				"discount_value_c=?,start_date_c=?,end_date_c=?,tag_id_c=?," +
				"title_c=?,description_c=? where id_c=?";
		PreparedStatement statement = conn.prepareStatement(sql);
		statement.setString(1, promotion.getDiscountType());
		statement.setString(2, promotion.getDiscountValue());
		statement.setTimestamp(3, new Timestamp(promotion.getStartDate().getTime()));
		statement.setTimestamp(4, new Timestamp(promotion.getEndDate().getTime()));
		statement.setLong(5, promotion.getTagId());
		statement.setString(6, promotion.getPromotionTitle());
		statement.setString(7, promotion.getPromotionDesc());
		statement.setLong(8, promotion.getPromotionId());
		statement.executeUpdate();
		statement.close();
		return null;
	}
	
	public String deletePromotion(long promotionId, Connection conn) throws SQLException
	{
		String sql = "delete from promotion_t where id_c=?";
		PreparedStatement statement = conn.prepareStatement(sql);
		statement.setLong(1, promotionId);
		statement.executeUpdate();
		statement.close();
		return null;
	}
	
	public String deletePromotions(long numIid, Connection conn) throws SQLException
	{
		String sql = "delete from promotion_t where num_iid_c=?";
		PreparedStatement statement = conn.prepareStatement(sql);
		statement.setLong(1, numIid);
		statement.executeUpdate();
		statement.close();
		return null;
	}
	
	public Promotion getPromotion(long numIid, Connection conn) throws SQLException
	{
		String sql = "select * from promotion_t where num_iid_c=?";
		PreparedStatement statement = conn.prepareStatement(sql);
		statement.setLong(1, numIid);
		ResultSet rs = statement.executeQuery();
		if (rs.next())
		{
			Promotion p = new Promotion();
			p.setNumIid(numIid);
			p.setPromotionId(rs.getLong("id_c"));
			p.setDiscountType(rs.getString("discount_type_c"));
			p.setDiscountValue(rs.getString("discount_value_c"));
			p.setStartDate(rs.getTimestamp("start_date_c"));
			p.setEndDate(rs.getTimestamp("end_date_c"));
			p.setPromotionTitle(rs.getString("title_c"));
			p.setPromotionDesc(rs.getString("description_c"));
			p.setTagId(1L);
			return p;
		}
		statement.close();
		rs.close();
		return null;
	}
	
	public Map<Long, com.cuxiaoyi.model.Item> getPromotedItems(long userId) throws NamingException, SQLException
	{
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "select * from promotion_t where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			rs = statement.executeQuery();
			Map<Long, com.cuxiaoyi.model.Item> items = new HashMap<Long, com.cuxiaoyi.model.Item>();
			while (rs.next())
			{
				long numIid = rs.getLong("num_iid_c");
				com.cuxiaoyi.model.Item item = items.get(numIid);
				if (item == null)
				{
					item = new com.cuxiaoyi.model.Item();
					item.setNumIid(numIid);
				}
				item.setPromotionId(rs.getLong("id_c"));
				item.setDiscountType(rs.getString("discount_type_c"));
				item.setDiscountValue(rs.getString("discount_value_c"));
				item.setStartDate(rs.getTimestamp("start_date_c"));
				item.setEndDate(rs.getTimestamp("end_date_c"));
				item.setPromotionTitle(rs.getString("title_c"));
				item.setPromotionDescription(rs.getString("description_c"));
				items.put(item.getNumIid(), item);
			}
			return items;
		}
		finally
		{
			DBUtils.close(conn, statement, rs);
		}
	}
	
	public void updateUser(long userId, String nick, String session) throws NamingException, SQLException
	{
		Connection conn = null;
		PreparedStatement statement = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "update user_t set session_c=?, last_login_c=? where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, session);
			statement.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			statement.setLong(3, userId);
			int result = statement.executeUpdate();
			if (result == 0)
			{
				sql = "insert into user_t(user_id_c, nick_c, session_c, last_login_c) values(?,?,?,?)";
				statement = conn.prepareStatement(sql);
				statement.setLong(1, userId);
				statement.setString(2, nick);
				statement.setString(3, session);
				statement.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
			}
			statement.executeUpdate();
		}
		finally
		{
			DBUtils.close(conn, statement, null);
		}
	}
	
	public PagingResult<User> getUsers(PagingOption option) throws NamingException, SQLException
	{
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "select * from user_t order by last_login_c limit ? offset ?";
			statement = conn.prepareStatement(sql);
			statement.setInt(1, option.getLimit());
			statement.setInt(2, option.getOffset());
			rs = statement.executeQuery();
			List<User> users = new ArrayList<User>();
			while (rs.next())
			{
				User user = new User();
				user.setNick(rs.getString("nick_c"));
				user.setLastLogin(rs.getDate("last_login_c"));
				users.add(user);
			}
			String totalSql = "select count(nick_c) from user_t";
			statement = conn.prepareStatement(totalSql);
			rs = statement.executeQuery();
			rs.next();
			long total = rs.getLong(1);
			PagingResult<User> result = new PagingResult<User>();
			result.setItems(users);
			result.setOption(option);
			result.setTotal(total);
			return result;
		}
		finally
		{
			DBUtils.close(conn, statement, rs);
		}
	}
}
